Ruminated Rumblings

If you've been following along on my Excel VBA App Script journey ( Part 1 , Part 2 ), I'm converting my old Excel VBA scripts to Google App Scripts. I'm specifically focused on Excel to Sheets at the moment but the general process applies to other App Script development. As I mentioned last time, once I figured out that you cannot use the integrated debugger without a wrapper routine, the next logical exercise was adding unit tests. The test wrapper is essentially ready to go as a unit test, it's just a question of how to formalize it a bit. At it's most basic, we could convert the test wrapper to return a pass/fail result and be done with it, ala: function   test_myFx () {    Logger . log ( "Test" )    return  myF x ( "U01" , SpreadsheetApp . getActiveSheet (). getRange ( 2 , 2 ). getValues ()) } function   myFx (findStr, findRange) { //Do some work } converts to: function   test_myFx () {    Logger . log ( "Test" )   if (myF x ( &q

 If you've been following along, I'm in the process of moving a library of Excel VBA scripts over to Google App Script (specifically Sheets). I've now successfully converted one of my key functions from VBA to App Script. The function itself is relatively basic - it's a version of VLOOKUP on steroids. Instead of returning a single matching item, it will return a delimited list of matching items. This is handy when you're trying to map a 1:many relationship. If you grok SQL, it's equivalent to: SELECT ROWID FROM ( some range) WHERE fieldName like "%pattern%" If someone finds it interesting / useful, let me know and I can share it. Once converted Google makes a very handy little "Debug" function available... except that you cannot actually use it for debugging code that's fully dynamic. I should not that it works if you aren't trying to pass in parameters from a spreadsheet. Maybe this is why everyone writes using code like "

As a long time (unfortunately read "old") user of Office products, I've done a lot of work in VBA to make the products more functional. I've been using Google Sheets sporadically as well, but never got around to using App Script until now. In the process of getting familiar with it, I found the information was widely spread and not very concise. Below are my observations for other would-be converters of VBA to App Script. Input parameters are a bit different from Excel VBA. You don’t get ranges but realized arrays of values. This is actually how VBA treats the range declaration but Google Sheets has ranges and arrays which throws the nomenclature just a bit. In the end, it’s passed the same, just watch for the term difference between the systems.  For instance, in VBA, the function: Function myFx (findStr As String , r As Range) Dim X As Long , found As Long found = 0 For X = 1 To r.Count     If r(X).Value = findStr Then       found = found + 1     End

Has anyone noticed that Delphi 2007 cannot append to a text file over 2GB in size? I freely admit that I had a problem in my code that created log files that large in the first place... but I also don't expect my compiler to blow up when dealing with files of any size. My first warning was when the append command started throwing I/O Error 131 messages. Error 131 is negative seek. The only way that could be is if CG has a signed int32 it's using for the filesize. When I hit the upper bounds of int32, it just wraps around to a negative number. In my case, I just fixed the bug and stopped making huge text files. Otherwise, it looks like I'd have to change how the append function works in the System library. Does anyone know if D2009 also has this problem?

In my ever expanding need for dealing with the outside world, I've discovered a quirky little thing about Indy 10. When using the TIdPOP3 component with Gmail, you have to manually call DisconnectNotifyPeer or delete commands are ignored. I suppose this isn't quirky as much as not really documented any place I was able to find. Let's backup and start at the beginning, shall we? I have a Windows service that polls a POP3 account looking for messages that it can import into a DB. It has all sorts of fun rules that can be assigned to discover what needs to be imported, what database it goes into, etc. At any rate, this code has been merrily chugging away on both Yahoo and SmarterMail servers with nary a hiccup. I recently needed to watch a Google domain app e-mail address and didn't think much of it. Of course, in order to do so you have to implement SSL for POP3. As I've posted in the past, this is not really much of an issue, you simply create the TIdSSLIOHandlerSock

RAID is a method that takes independent drives and lets a system group them together for security (redundancy or parity), speed enhancements, storage space increases or all three. One of the long-time stalwarts of the RAID environment is RAID 5. In RAID 5, you need at least 3 identically sized disks. They are combined so that the storage space is N-1 (i.e., in a 3-drive system, total space is 2x drive size). The last disk is used for parity. With a parity drive, you can remove any one of the drives and still have access to your data. If you remove two or more of the drives though, you'd better have a good backup. How does this work? Through the magic of XOR. The following statements are all true: A XOR B = PAR PAR XOR B = A A XOR PAR = B That's how parity lets you lose one disk and still recover your data. The same rules also apply in larger sets. Notice that you have to rotate the position of the parity data though. So a 5 drive system looks like: A XOR B XOR C